﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SistemadeEstágios.Model;

namespace SistemadeEstágios.DAL
{
    /// <summary>
    /// Factory de acesso a dados dos responsaveis
    /// </summary>
    public class ResponsaveisBD : BaseBD
    {

        public Responsaveis Buscar(int codigo)
        {
            Responsaveis obj;

            string query = @"SELECT Res.codigo As Codigo, Res.nome As Nome, Res.telefone As Telefone, Res.email As Email,
                            Res.curso As CodCurso, Cur.nome As Curso, Res.dt_insercao As DtIns FROM Responsaveis AS Res 
                            Inner Join Curso As Cur ON Res.curso = Cur.codigo Where Res.codigo = @codigo;";
            try
            {
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", codigo);
                oDbManager.ExecuteReader(query, System.Data.CommandType.Text);

                if (oDbManager.DBReader.Read())
                {
                    obj = new Responsaveis();

                    obj.Codigo = Int32.Parse(oDbManager.DBReader["Codigo"].ToString());
                    obj.Nome = oDbManager.DBReader["Nome"].ToString();
                    obj.Telefone = oDbManager.DBReader["Telefone"].ToString();
                    obj.Email = oDbManager.DBReader["Email"].ToString();
                    obj.Curso = new Cursos();
                    obj.Curso.Codigo = Int32.Parse(oDbManager.DBReader["CodCurso"].ToString());
                    obj.Curso.Nome = oDbManager.DBReader["Curso"].ToString();

                    return obj;
                }
                else
                    return null;
            }
            finally
            {
                oDbManager.CloseConnection();
            }
        }

        public List<Responsaveis> Buscar()
        {
            List<Responsaveis> lista = null;
            Responsaveis obj;

            string query = @"SELECT Res.codigo As Codigo, Res.nome As Nome, Res.telefone As Telefone, Res.email As Email,
                            Res.curso As Curso, Res.dt_insercao As DtIns FROM Responsaveis AS Res 
                            Inner Join Curso As Cur ON Res.curso = Cur.codigo;";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.ExecuteReader(query);

                while (oDbManager.DBReader.Read())
                {
                    if (lista == null) lista = new List<Responsaveis>();

                    obj = new Responsaveis();

                    obj.Codigo = Int32.Parse(oDbManager.DBReader["Codigo"].ToString());
                    obj.Nome = oDbManager.DBReader["Nome"].ToString();
                    obj.Telefone = oDbManager.DBReader["Telefone"].ToString();
                    obj.Email = oDbManager.DBReader["Email"].ToString();
                    obj.Curso = new Cursos();
                    obj.Curso.Codigo = Int32.Parse(oDbManager.DBReader["CodCurso"].ToString());
                    obj.Curso.Nome = oDbManager.DBReader["Curso"].ToString();

                    lista.Add(obj);
                }
            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return lista;
        }

        public bool Insere(Responsaveis obj)
        {
            int r = -1;
            string query = @"INSERT INTO responsaveis (nome, 
                telefone, email, curso, dt_insercao)
                VALUES (@nome, @telefone, @email, @curso, @dt_insercao);";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@nome", obj.Nome);
                oDbManager.AddParameter("@telefone", obj.Telefone);
                oDbManager.AddParameter("@email", obj.Email);
                oDbManager.AddParameter("@curso", obj.Curso);
                oDbManager.AddParameter("@dt_insercao", DateTime.Now);

                r = oDbManager.ExecuteNonQuery(query, System.Data.CommandType.Text);

            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return (r > 0);
        }

        public bool CriaHistorico(int codigo)
        {
            int r = -1;
            string query = @"Insert Into Hst_Responsaveis SELECT (Select Max(codigo) + 1 from Hst_Responsaveis), 
                codigo, nome, telefone, email, curso, now() FROM Responsaveis Where codigo = @codigo";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", codigo);

                r = oDbManager.ExecuteNonQuery(query, System.Data.CommandType.Text);
            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return (r > 0);
        }

        public bool Alterar(Responsaveis resp)
        {
            int r = -1;
            string query = string.Empty;

            try
            {
                query = @"UPDATE Responsaveis SET nome = @nome, telefone = @telefone,
                        email = @email, curso = @curso  WHERE codigo = @codigo;";

                oDbManager.OpenConnection();
                oDbManager.AddParameter("@nome", resp.Nome);
                oDbManager.AddParameter("@telefone", resp.Telefone);
                oDbManager.AddParameter("@email", resp.Email);
                oDbManager.AddParameter("@curso", resp.Curso.Codigo);
                oDbManager.AddParameter("@codigo", resp.Codigo);

                r = oDbManager.ExecuteNonQuery(query, System.Data.CommandType.Text);

            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return (r > 0);
        }

        public int Ultimo()
        {
            int retorno = 1;
            string query = @"Select Max(codigo) + 1 From Responsaveis";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.ExecuteReader(query);

                if (oDbManager.DBReader.Read())
                {
                    if (oDbManager.DBReader[0] != DBNull.Value)
                        retorno = Int32.Parse(oDbManager.DBReader[0].ToString());
                }
            }
            finally
            {
                oDbManager.CloseConnection();
            }
            return retorno;
        }
    }
}
